import backtrader as bt
import akshare as ak
import pandas as pd
import datetime  #
import os.path  # 路径管理
import sys  # 获取当前运行脚本的路径 (in argv[0])

import baostock as bs
import SimpleMySqlClass as smc


# 1, get akshare stock data
def get_stock_data():
    code = "002202"
    stock_hfq_df = ak.stock_zh_a_hist(
        symbol=code, adjust="qfq", start_date="20230101", end_date="20231230"
    )
    # print(stock_hfq_df)
    stock_hfq_df = stock_hfq_df.iloc[:, 0:7]
    # stock_hfq_df.日期 = pd.to_datetime(stock_hfq_df.日期)

    # 日期     开盘     收盘     最高     最低     成交量  成交额    振幅   涨跌幅   涨跌额   换手率
    # 列名记得这样定义好
    stock_hfq_df.columns = ["date", "open", "close", "high", "low", "volume", "amount"]
    # stock_hfq_df.set_index("Date", inplace=True)
    print(stock_hfq_df)
    save_data_to_mysql(code, stock_hfq_df)


# 2, save data to mysql
def save_data_to_mysql(code, stock_data_df):
    mysql_obj = get_mysql_handle()
    # date time code open high low close volume amount adjustflag
    # INSERT INTO stocks.t_stock_k_minute_data (id, sd_date, sd_date_time, sd_code, sd_symbol, sd_name, sd_open, sd_high, sd_low, sd_close, sd_volume, sd_amount, sd_adjust_flag, sd_data_type, create_time, update_time)

    for index, row in stock_data_df.iterrows():
        insertSql = """INSERT INTO t_stock_k_data 
            (id, sd_date, sd_code, sd_open, sd_high, sd_low, sd_close, sd_volume, sd_amount,create_time) 
            VALUES 
            (0, %s, %s, %s,%s,%s, %s,%s, %s,%s) """

        s_data = (
            (
                row["date"],
                code,
                row["open"],
                row["high"],
                row["low"],
                row["close"],
                row["volume"],
                row["amount"],
                datetime.datetime.now(),
            ),
        )
        # print("sql = ", insertSql)
        rs = mysql_obj.executeMany(insertSql, s_data)
        print("rs = ", rs)
    print("-------------------------------")
    read_data_from_mysql(mysql_obj)
    close_mysql_handle(mysql_obj)

    pass


def read_data_from_mysql(mysql_obj):
    tableName = "t_stock_k_data"
    sql = f"select sd_date, sd_code, sd_open, sd_high, sd_low, sd_close, sd_volume, sd_amount from {tableName}"
    rs = mysql_obj.query_sql(sql)
    print("query rs = ", rs)
    pass


def get_mysql_handle():
    mysql_obj = smc.SimpleMySqlClass("127.0.0.1", "stocks", "root", "123456")
    return mysql_obj


def close_mysql_handle(mysql_obj):
    mysql_obj.close()
    pass


# 3, query data from mysql

if __name__ == "__main__":
    get_stock_data()
    print("main")
